<?php

namespace Icsoc\ReportBundle\Model;

use Icsoc\DataBundle\Model\BaseModel;
use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Class AgentModel
 *
 * @package Icsoc\ReportBundle\Model
 */
class AgentModel extends BaseModel
{
    /** @var \Doctrine\DBAL\Connection */
    private $dbal;

    /**
     * @param ContainerInterface $container
     */
    public function __construct(ContainerInterface $container)
    {
        $this->container = $container;
        $this->dbal = $this->container->get('doctrine.dbal.default_connection');
    }

    /**
     * 坐席工作表现报表月数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getAgentMonthData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/"; //2015-05 格式判断
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = 'vcc_id = :vcc_id ';
        $condition = array('vcc_id' => $vccId);
        $agStaCondition['condition'] = '';
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_date']) && !empty($info['filter']['start_date'])) {
                $startDate = $info['filter']['start_date'];
                $msg = $this->container->get('icsoc_data.helper')->regexRormat($reg, $startDate, '开始日期不正确', 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_date >= :start_date ";
                $condition['start_date'] = $startDate;
                $agStaCondition['condition'] .= ' AND start_time >= "'.strtotime($startDate).'"';
            }
            //结束时间
            if (isset($info['filter']['end_date']) && !empty($info['filter']['end_date'])) {
                $endDate = $info['filter']['end_date'];
                $msg = $this->container->get('icsoc_data.helper')->regexRormat($reg, $endDate, '结束日期不正确', 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_date <= :end_date ";
                $condition['end_date'] = $endDate;
                $agStaCondition['condition'] .= ' AND start_time <= "'.strtotime("$endDate +1 month -1 seconds").'"';
            }
            //坐席id;
            if (isset($info['filter']['ag_id']) && !empty($info['filter']['ag_id'])) {
                $where .= " AND ag_id IN (".$info['filter']['ag_id'].")";
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        $total = count($this->dbal->fetchAll(
            'SELECT count(*) '.
            'FROM rep_agent_day '.
            'WHERE '.$where.
            ' GROUP BY start_date,ag_id ',
            $condition
        ));
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($total, 'rep_agent_day', $info);
        $field = array(
            'in_num', 'out_num', 'out_calls', 'out_secs', 'conn_secs', 'internal_num', 'ring_num',
            'ring_secs', 'consult_num', 'consult_secs', 'hold_num', 'hold_secs', 'conference_num', 'conference_secs',
            'shift_num', 'login_secs', 'ready_secs', 'busy_secs', 'wait_num', 'wait_secs', '`evaluate_-4`', '`evaluate_-3`',
            '`evaluate_-2`', '`evaluate_-1`', 'evaluate_0', 'evaluate_1', 'evaluate_2', 'evaluate_3', 'evaluate_4',
            'evaluate_5', 'evaluate_6', 'evaluate_7', 'evaluate_8', 'evaluate_9', 'refuse_num',
        );
        $fields = array();
        foreach ($field as $v) {
            $fields[] = "SUM($v) AS $v";
        }

        $fields = implode(',', $fields);

        if (empty($info['export'])) {
            $limit = ($page['limit'] == -1) ? '' : ' LIMIT '.$page['start'].','.$page['limit'];
            $result = $this->dbal->fetchAll(
                "SELECT $fields,ag_id,ag_num,ag_name,start_date ".
                'FROM rep_agent_day '.
                'WHERE '.$where.
                'GROUP BY start_date,ag_id '.
                ' ORDER BY '.$page['sort'].
                $limit,
                $condition
            );
        } else {
            $result = $this->dbal->fetchAll(
                "SELECT $fields,ag_id,ag_num,ag_name,start_date ".
                'FROM rep_agent_day '.
                'WHERE '.$where.
                'GROUP BY start_date,ag_id '.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        }

        $data = array();

        /** @var  $agentStaReason (坐席状态原因) */
        $agStaReason = $this->container->get('icsoc_data.model.report')->getAgentStaReason($vccId);
        $agStaCondition['vcc_id'] = $vccId;
        $agStaCondition['type'] = 'month';
        $agStaCondition['reason'] = $agStaReason;
        /** @var  $agStaDetail (坐席状态明细) */
        $agStaDetail = $this->container->get('icsoc_data.model.report')->getAgentStaDetail($agStaCondition);

        foreach ($result as $key => $v) {
            foreach ($agStaReason as $reasonId => $reasonName) {
                $v['agstanum'.$reasonId] = 0;
                $v['agstaduration'.$reasonId] = 0;
                if (isset($agStaDetail[$v['start_date']][$v['ag_id']][$reasonId]['num'])) {
                    $v['agstanum'.$reasonId] = $agStaDetail[$v['start_date']][$v['ag_id']][$reasonId]['num'];
                }

                if (isset($agStaDetail[$v['start_date']][$v['ag_id']][$reasonId]['secs'])) {
                    $v['agstaduration'.$reasonId] = $agStaDetail[$v['start_date']][$v['ag_id']][$reasonId]['secs'];
                }
            }
            $v['agstanum_other'] = isset($agStaDetail[$v['start_date']][$v['ag_id']]['other']['num']) ?
                $agStaDetail[$v['start_date']][$v['ag_id']]['other']['num'] : 0;
            $v['agstaduration_other'] = isset($agStaDetail[$v['start_date']][$v['ag_id']]['other']['secs']) ?
                $agStaDetail[$v['start_date']][$v['ag_id']]['other']['secs'] : 0;

            $data[$key] = $v;
            $data[$key]['date'] = $v['start_date'];
        }
        $data = $this->processData($data);

        return array(
            'code' => 200,
            'total' => $page['totalPage'],
            'page' => $page['page'],
            'records' => $total,
            'rows' => $data,
        );
    }

    /**
     * 坐席工作表现报表天数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getAgentDayData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = 'vcc_id = :vcc_id ';
        $condition = array('vcc_id' => $vccId);
        $agStaCondition['condition'] = '';
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_date']) && !empty($info['filter']['start_date'])) {
                $startDate = $info['filter']['start_date'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND nowdate >= :start_date ";
                $condition['start_date'] = $startDate;
                $agStaCondition['condition'] .= ' AND start_time >= "'.strtotime($startDate).'"';
            }
            //结束时间
            if (isset($info['filter']['end_date']) && !empty($info['filter']['end_date'])) {
                $endDate = $info['filter']['end_date'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND nowdate <= :end_date ";
                $condition['end_date'] = $endDate;
                $agStaCondition['condition'] .= ' AND start_time <= "'.strtotime("$endDate +1 day -1 seconds").'"';
            }

            if (isset($info['filter']['ag_id']) && !empty($info['filter']['ag_id'])) {
                $where .= " AND ag_id IN (".$info['filter']['ag_id'].")";
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        $total = $this->dbal->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_agent_day '.
            'WHERE '.$where,
            $condition
        );
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($total, 'rep_agent_day', $info);
        if (empty($info['export'])) {
            $limit = ($page['limit'] == -1) ? '' : ' LIMIT '.$page['start'].','.$page['limit'];
            $result = $this->dbal->fetchAll(
                'SELECT * '.
                'FROM rep_agent_day '.
                'WHERE '.$where.
                ' ORDER BY '.$page['sort'].
                $limit,
                $condition
            );
        } else {
            $result = $this->dbal->fetchAll(
                'SELECT * '.
                'FROM rep_agent_day '.
                'WHERE '.$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        }

        $data = array();

        /** @var  $agentStaReason (坐席状态原因) */
        $agStaReason = $this->container->get('icsoc_data.model.report')->getAgentStaReason($vccId);
        $agStaCondition['vcc_id'] = $vccId;
        $agStaCondition['type'] = 'day';
        $agStaCondition['reason'] = $agStaReason;
        /** @var  $agStaDetail (坐席状态明细) */
        $agStaDetail = $this->container->get('icsoc_data.model.report')->getAgentStaDetail($agStaCondition);

        foreach ($result as $key => $v) {
            foreach ($agStaReason as $reasonId => $reasonName) {
                $v['agstanum'.$reasonId] = 0;
                $v['agstaduration'.$reasonId] = 0;
                if (isset($agStaDetail[$v['nowdate']][$v['ag_id']][$reasonId]['num'])) {
                    $v['agstanum'.$reasonId] = $agStaDetail[$v['nowdate']][$v['ag_id']][$reasonId]['num'];
                }

                if (isset($agStaDetail[$v['nowdate']][$v['ag_id']][$reasonId]['secs'])) {
                    $v['agstaduration'.$reasonId] = $agStaDetail[$v['nowdate']][$v['ag_id']][$reasonId]['secs'];
                }
            }
            $v['agstanum_other'] = isset($agStaDetail[$v['nowdate']][$v['ag_id']]['other']['num']) ?
                $agStaDetail[$v['nowdate']][$v['ag_id']]['other']['num'] : 0;
            $v['agstaduration_other'] = isset($agStaDetail[$v['nowdate']][$v['ag_id']]['other']['secs']) ?
                $agStaDetail[$v['nowdate']][$v['ag_id']]['other']['secs'] : 0;

            $data[$key] = $v;
            $data[$key]['date'] = $v['nowdate'];
        }
        $data = $this->processData($data);

        return array(
            'code' => 200,
            'total' => $page['totalPage'],
            'page' => $page['page'],
            'records' => $total,
            'rows' => $data,
        );
    }

    /**
     * 坐席工作表现报表小时数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getAgentHourData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = 'vcc_id = :vcc_id ';
        $condition = array('vcc_id' => $vccId);
        $agStaCondition['condition'] = '';
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_date']) && !empty($info['filter']['start_date'])) {
                $startDate = $info['filter']['start_date'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_date >= :start_date ";
                $condition['start_date'] = $startDate;
                $agStaCondition['condition'] .= ' AND start_time >= "'.strtotime($startDate).'"';
            }
            //结束时间
            if (isset($info['filter']['end_date']) && !empty($info['filter']['end_date'])) {
                $endDate = $info['filter']['end_date'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_date <= :end_date ";
                $condition['end_date'] = $endDate;
                $agStaCondition['condition'] .= ' AND start_time <= "'.strtotime("$endDate +1 day -1 seconds").'"';
            }

            if (isset($info['filter']['ag_id']) && !empty($info['filter']['ag_id'])) {
                $where .= " AND ag_id IN (".$info['filter']['ag_id'].")";
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        $total = $this->dbal->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_agent_hour '.
            'WHERE '.$where,
            $condition
        );
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($total, 'rep_agent_hour', $info);
        if (empty($info['export'])) {
            $limit = ($page['limit'] == -1) ? '' : ' LIMIT '.$page['start'].','.$page['limit'];
            $result = $this->dbal->fetchAll(
                'SELECT * '.
                'FROM rep_agent_hour '.
                'WHERE '.$where.
                ' ORDER BY '.$page['sort'].
                $limit,
                $condition
            );
        } else {
            $result = $this->dbal->fetchAll(
                'SELECT * '.
                'FROM rep_agent_hour '.
                'WHERE '.$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        }

        $data = array();

        /** @var  $agentStaReason (坐席状态原因) */
        $agStaReason = $this->container->get('icsoc_data.model.report')->getAgentStaReason($vccId);
        $agStaCondition['vcc_id'] = $vccId;
        $agStaCondition['type'] = 'hour';
        $agStaCondition['reason'] = $agStaReason;
        /** @var  $agStaDetail (坐席状态明细) */
        $agStaDetail = $this->container->get('icsoc_data.model.report')->getAgentStaDetail($agStaCondition);

        foreach ($result as $key => $v) {
            $formatDate = $v['start_date'].' '.sprintf("%02d", $v['time_stamp']);
            foreach ($agStaReason as $reasonId => $reasonName) {
                $v['agstanum'.$reasonId] = 0;
                $v['agstaduration'.$reasonId] = 0;
                if (isset($agStaDetail[$formatDate][$v['ag_id']][$reasonId]['num'])) {
                    $v['agstanum'.$reasonId] = $agStaDetail[$formatDate][$v['ag_id']][$reasonId]['num'];
                }

                if (isset($agStaDetail[$formatDate][$v['ag_id']][$reasonId]['secs'])) {
                    $v['agstaduration'.$reasonId] = $agStaDetail[$formatDate][$v['ag_id']][$reasonId]['secs'];
                }
            }
            $v['agstanum_other'] = isset($agStaDetail[$formatDate][$v['ag_id']]['other']['num']) ?
                $agStaDetail[$formatDate][$v['ag_id']]['other']['num'] : 0;
            $v['agstaduration_other'] = isset($agStaDetail[$formatDate][$v['ag_id']]['other']['secs']) ?
                $agStaDetail[$formatDate][$v['ag_id']]['other']['secs'] : 0;

            $data[$key] = $v;
            $data[$key]['date'] = $v['start_date'].' '.sprintf("%02d", $v['time_stamp']).'时';
        }
        $data = $this->processData($data);

        return array(
            'code' => 200,
            'total' => $page['totalPage'],
            'page' => $page['page'],
            'records' => $total,
            'rows' => $data,
        );
    }

    /**
     * 坐席工作表现报表半小时数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getAgentHalfHourData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = 'vcc_id = :vcc_id ';
        $condition = array('vcc_id' => $vccId);
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_date']) && !empty($info['filter']['start_date'])) {
                $startDate = $info['filter']['start_date'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_date >= :start_date ";
                $condition['start_date'] = $startDate;
            }
            //结束时间
            if (isset($info['filter']['end_date']) && !empty($info['filter']['end_date'])) {
                $endDate = $info['filter']['end_date'];
                $msg = $this->container->get('icsoc_data.helper')->isDate('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_date <= :end_date ";
                $condition['end_date'] = $endDate;
            }

            if (isset($info['filter']['ag_id']) && !empty($info['filter']['ag_id'])) {
                $where .= " AND ag_id IN (".$info['filter']['ag_id'].")";
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        $total = $this->dbal->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_agent_halfhour '.
            'WHERE '.$where,
            $condition
        );
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($total, 'rep_agent_halfhour', $info);
        if (empty($info['export'])) {
            $limit = ($page['limit'] == -1) ? '' : ' LIMIT '.$page['start'].','.$page['limit'];
            $result = $this->dbal->fetchAll(
                'SELECT * '.
                'FROM rep_agent_halfhour '.
                'WHERE '.$where.
                ' ORDER BY '.$page['sort'].
                $limit,
                $condition
            );
        } else {
            $result = $this->dbal->fetchAll(
                'SELECT * '.
                'FROM rep_agent_halfhour '.
                'WHERE '.$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        }
        $data = array();
        foreach ($result as $key => $v) {
            $data[$key] = $v;
            $hour = sprintf("%02d", floor($v['time_stamp'] / 2)).":";
            $minute = $v['time_stamp'] % 2 == 1 ? "30" : "00";
            $data[$key]['date'] = $v['start_date']." ".$hour.$minute;
        }
        $data = $this->processData($data);

        return array(
            'code' => 200,
            'total' => $page['totalPage'],
            'page' => $page['page'],
            'records' => $total,
            'rows' => $data,
        );
    }

    /**
     * 公共整理数据
     *
     * @param $data
     *
     * @return array
     */
    private function processData($data)
    {
        $param = array(
            'data' => $data,
            'report_name' => 'agent',
        );
        $gridData = array();
        $rst = $this->container->get('icsoc_report.model.report')->getDataReport($param);
        if (isset($rst['code']) && $rst['code'] == 200) {
            $gridData = $rst['data'];
        }

        return $gridData;
    }
}
